# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

import pymysql

from XCFScrapy import settings


class DBPipeline(object):
    def __init__(self):
        # 连接数据库
        self.connect = pymysql.connect(
            host=settings.MYSQL_HOST,
            db=settings.MYSQL_DBNAME,
            user=settings.MYSQL_USER,
            passwd=settings.MYSQL_PASSWD,
            charset='utf8',
            use_unicode=True)

        # 通过cursor执行增删查改
        self.cursor = self.connect.cursor()

class XcfscrapyPipeline(DBPipeline):
    def process_item(self, item, spider):
        if(spider.name == "bannerspider"):
            self.save_bannerdata(item)
        elif(spider.name == "foodmaterialspider"):
            self.save_foodmaterialcategroydata(item)
        elif (spider.name == "foodsCategoryspider"):
            self.save_foodcategroydata(item)
        elif (spider.name == "foodsspider"):
            self.save_foodsdata(item)
        elif (spider.name == "Categoryspider"):
            self.save_foodsdata(item)

    # 保存食材分类到数据
    def save_foodmaterialcategroydata(self,item):
        self.cursor.execute("INSERT INTO  xiaChuFang_materialcategroy (name,url,foodId,prentFoodId) values(%s,%s,%s,%s)",
                            (item['name'], item['url'],item['foodId'],item['prentFoodId']))
        self.connect.commit()

    # 保存分类到数据
    def save_foodcategroydata(self, item):
        self.cursor.execute(
                "INSERT INTO  xiaChuFang_activitycategroy (title,url,categoryId) values(%s,%s,%s)",
                (item['title'], item['url'], item['categoryId'],))
        self.connect.commit()

    # 保存食材分类对应的菜谱到数据库
    def save_foodsdata(self,item):
        self.cursor.execute(
            "INSERT INTO  xiaChuFang_foods (foodId,name,img,material,number,author,foodDetailurl) values(%s,%s,%s,%s,%s,%s,%s)",
            (item['foodId'], item['name'], item['img'], item['material'], item['number'],item['author'],item['foodDetailurl']))
        self.connect.commit()

    # 保存轮播图到数据库
    def save_bannerdata(self,item):
        self.cursor.execute(
            "INSERT INTO  user_operation_banner (title,img,url,author) values(%s,%s,%s,%s)",
            (item['title'], item['img'], item['url'], item['author']))
        self.connect.commit()